﻿using Microsoft.SqlServer.Management.Common; //在microsoft.sqlserver.smo.dll中  
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Smo; //需添加microsoft.sqlserver.smo.dll的引用  
using Microsoft.SqlServer.Management.Smo.Agent;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.IO; //需添加microsoft.sqlserver.connectioninfo.dll的引用
using System.Linq;
using System.Text;

public class SMO
{
    private static Server server = null;
    private static string instanceName;
    private static string remoteSvrName;
    private static bool loginSecure;
    private ConnectionType connectionType;

    private enum ConnectionType
    {
        ConnectToLocalIntergated,
        ConnectionString,
        ConnectToLocalDefaultInstance,
        ConnectToLocalNamedInstance,
        ConnectToRemoteServer
    }

    /// <summary>
    /// 封装了SQL和查询参数的结构体
    /// </summary>
    public struct ExecuteableUnit
    {
        public string SQL;
        public SqlParameter[] param;
    }

    public SMO()
    {
        this.connectionType = ConnectionType.ConnectToLocalIntergated;
    }

    public SMO(string connectionString)
    {
        this.connectionType = ConnectionType.ConnectionString;
        SMO.connectionString = connectionString;
    }

    public SMO(string loginName, string loginPassword)
    {
        this.connectionType = ConnectionType.ConnectToLocalDefaultInstance;
        SMO.loginName = loginName;
        SMO.loginPassword = loginPassword;
    }

    public SMO(string instanceName, string loginName, string loginPassword)
    {
        this.connectionType = ConnectionType.ConnectToLocalNamedInstance;
        SMO.instanceName = instanceName;
        SMO.loginName = loginName;
        SMO.loginPassword = loginPassword;
    }

    public SMO(string remoteSvrName, string loginName, string loginPassword, bool loginSecure = false)
    {
        this.connectionType = ConnectionType.ConnectToRemoteServer;
        SMO.remoteSvrName = remoteSvrName;
        SMO.loginSecure = loginSecure;
        SMO.loginName = loginName;
        SMO.loginPassword = loginPassword;
    }

    ~SMO()
    {
        Disconnect();
    }

    public ServerActiveDirectory ServerActiveDirectory
    {
        get
        {
            try
            {
                Connect();
                return server.ActiveDirectory;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public AffinityInfo ServerAffinityInfo
    {
        get
        {
            try
            {
                Connect();
                return server.AffinityInfo;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public AuditLevel ServerAuditLevel
    {
        get
        {
            try
            {
                Connect();
                return server.AuditLevel;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public AuditCollection ServerAudits
    {
        get
        {
            try
            {
                Connect();
                return server.Audits;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public AvailabilityGroupCollection ServerAvailabilityGroups
    {
        get
        {
            try
            {
                Connect();
                return server.AvailabilityGroups;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public BackupDeviceCollection ServerBackupDevices
    {
        get
        {
            try
            {
                Connect();
                return server.BackupDevices;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public string ServerBackupDirectory
    {
        get
        {
            try
            {
                Connect();
                return server.BackupDirectory;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public DatabaseCollection ServerDatabases
    {
        get
        {
            try
            {
                Connect();
                return server.Databases;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public Server ServerAlter()
    {
        try
        {
            Connect();
            server.Alter();
            return server;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public Server ServerAlter(bool overrideValueChecking)
    {
        try
        {
            Connect();
            server.Alter(overrideValueChecking);
            return server;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public List<object> ServerDiscover()
    {
        try
        {
            Connect();
            return server.Discover();
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public Server ServerKillAllProcesses(string databaseName)
    {
        try
        {
            Connect();
            server.KillAllProcesses(databaseName);
            return server;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public Server ServerKillDatabase(string databaseName)
    {
        try
        {
            Connect();
            server.KillDatabase(databaseName);
            return server;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public Server ServerKillProcess(int processId)
    {
        try
        {
            Connect();
            server.KillProcess(processId);
            return server;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public LinkedServerCollection ServerLinkedServers
    {
        get
        {
            try
            {
                Connect();
                return server.LinkedServers;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public string ServerCollation
    {
        get
        {
            try
            {
                Connect();
                return server.Collation;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public int ServerCollationID
    {
        get
        {
            try
            {
                Connect();
                return server.CollationID;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public ServerConnection ServerConnectionContext
    {
        get
        {
            try
            {
                Connect();
                return server.ConnectionContext;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public CredentialCollection ServerCredentials
    {
        get
        {
            try
            {
                Connect();
                return server.Credentials;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public int ServerProcessors
    {
        get
        {
            try
            {
                Connect();
                return server.Processors;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public int ServerProcessorUsage
    {
        get
        {
            try
            {
                Connect();
                return server.ProcessorUsage;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public string ServerProduct
    {
        get
        {
            try
            {
                Connect();
                return server.Product;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public string ServerProductLevel
    {
        get
        {
            try
            {
                Connect();
                return server.ProductLevel;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public SqlPropertyCollection ServerProperties
    {
        get
        {
            try
            {
                Connect();
                return server.Properties;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public Settings ServerSettings
    {
        get
        {
            try
            {
                Connect();
                return server.Settings;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public DatabaseEngineType ServerType
    {
        get
        {
            try
            {
                Connect();
                return server.ServerType;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public Version ServerVersion
    {
        get
        {
            try
            {
                Connect();
                return server.Version;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 数据库重命名
    /// </summary>
    /// <param name="newName">新的数据库名</param>
    /// <returns>返回数据库对象引用</returns>
    public Database DatabaseRename(string newName)
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                db.Rename(newName);
                return db;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public ColumnCollection TableColumns
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.Tables[TableName].Columns;
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public TableEvents TableEvents
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.Tables[TableName].Events;
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public IndexCollection TableIndexes
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.Tables[TableName].Indexes;
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public ForeignKeyCollection TableForeignKeys
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.Tables[TableName].ForeignKeys;
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public SqlPropertyCollection TableProperties
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.Tables[TableName].Properties;
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public long TableRowCount
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.Tables[TableName].RowCount;
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public double TableRowCountAsDouble
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.Tables[TableName].RowCountAsDouble;
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 表触发器
    /// </summary>
    public Trigger TableTrigger
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                {
                    Table table = db.Tables[TableName];
                    if (table != null)
                    {
                        Trigger trigger = table.Triggers[TriggerName];
                        if (trigger != null)
                            return trigger;
                        else
                            throw new InvalidDataException(TriggerName);
                    }
                    else
                        throw new InvalidDataException(TableName);
                }
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 表中所有触发器
    /// </summary>
    public TriggerCollection TableTriggers
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                {
                    Table table = db.Tables[TableName];
                    if (table != null)
                        return table.Triggers;
                    else
                        throw new InvalidDataException(TableName);
                }
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public Table TableAlter()
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                table.Alter();
                return table;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public Table TableAlterWithNoCheck()
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                table.AlterWithNoCheck();
                return table;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public Table TableCheckTable()
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                table.CheckTable();
                return table;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public string TableCreationScript
    {
        get
        {
            try
            {
                if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                    throw new ArgumentNullException(DatabaseName);
                if (TableName == null || TableName.Trim() == string.Empty)
                    throw new ArgumentNullException(TableName);
                Connect();
                Database db = server.Databases[DatabaseName];
                Table table;
                if (db != null)
                    table = db.Tables[TableName];
                else
                    throw new InvalidDataException(DatabaseName);
                //初始化脚本生成器
                Scripter scripter = new Scripter(server);
                //下面这些是可选项：作用是向脚本生成器中添加需要生成的脚本内容
                //Default 获取或设置布尔属性值指定创建所引用对象是否包含在生成的脚本。
                scripter.Options.Add(ScriptOption.ContinueScriptingOnError);
                //ContinueScriptingOnError 获取或设置布尔属性值指定的脚本是否继续操作时遇到错误后。
                scripter.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
                //ConvertUserDefinedDataTypesToBaseType 获取或设置布尔属性值指定是否将用户定义的数据类型转换成最合适的SQL Server基本数据类型生成的脚本中。
                // IncludeIfNotExists 获取或设置一个布尔属性值，指定包括它在脚本之前，是否检查一个对象是否存在。
                scripter.Options.Add(ScriptOption.ExtendedProperties);
                //声明统一资源名称集合对象
                UrnCollection collection = new UrnCollection();
                //The UrnCollection class represents a collection of Urn objects that represent Uniform Resource Name (URN) addresses.
                collection.Add(table.Urn);

                //声明字符串集合对象：存储collection中的所有string对象（在这里其中有3个string对象）
                StringCollection sqls = scripter.Script(collection);
                StringBuilder sb = new StringBuilder();
                //遍历字符串集合对象sqls中的string对象，选择要输出的脚本语句：
                foreach (string s in sqls)
                    sb.Append(s).Append("\r\nGO\r\n\r\n");
                return sb.ToString();
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public Table TableRebuild()
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                table.Rebuild();
                return table;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public Table TableRebuild(int partitionNumber)
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                table.Rebuild(partitionNumber);
                return table;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public Table TableRebuildIndexes()
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                table.RebuildIndexes(20);
                return table;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public Table TableRename(string newname)
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                table.Rename(newname);
                return table;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public Table TableRecalculateSpaceUsage()
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                table.RecalculateSpaceUsage();
                return table;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 更新数据库表的索引统计状态
    /// </summary>
    /// <returns></returns>
    public Table TableUpdateStatistics()
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                table.UpdateStatistics();
                return table;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 视图重命名
    /// </summary>
    /// <param name="newName">视图新名称</param>
    /// <returns></returns>
    public View ViewRename(string newName)
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                View view = db.Views[ViewName];
                view.Rename(newName);
                return view; ;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public View ViewReCompileReferences()
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                View view = db.Views[ViewName];
                view.ReCompileReferences();
                return view;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 更新数据库视图的索引统计状态
    /// </summary>
    /// <returns></returns>
    public View ViewUpdateStatistics()
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                View view = db.Views[ViewName];
                view.UpdateStatistics();
                return view;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public Server @Server
    {
        get
        {
            try
            {
                Connect();
                return server;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public Database @Database
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                return db;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public DatabaseCollection Databases
    {
        get
        {
            try
            {
                Connect();
                return server.Databases;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public StoredProcedure @StoredProcedure
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.StoredProcedures[StoredProcedureName];
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public StoredProcedureCollection StoredProcedures
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.StoredProcedures;
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public Table @Table
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.Tables[TableName];
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public TableCollection Tables
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.Tables;
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public View @View
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.Views[ViewName];
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public ViewCollection Views
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.Views;
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public Index ViewIndex
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                {
                    View view = db.Views[ViewName];
                    if (view != null)
                    {
                        Index index = view.Indexes[IndexName];
                        if (index != null)
                            return index;
                        else
                            throw new InvalidDataException(IndexName);
                    }
                    else
                        throw new InvalidDataException(ViewName);
                }
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public IndexCollection ViewIndexes
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                {
                    View view = db.Views[ViewName];
                    if (view != null)
                        return view.Indexes;
                    else
                        throw new InvalidDataException(ViewName);
                }
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 视图触发器
    /// </summary>
    public Trigger ViewTrigger
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                {
                    View view = db.Views[ViewName];
                    if (view != null)
                    {
                        Trigger trigger = view.Triggers[TriggerName];
                        if (trigger != null)
                            return trigger;
                        else
                            throw new InvalidDataException(TriggerName);
                    }
                    else
                        throw new InvalidDataException(ViewName);
                }
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 视图中所有触发器
    /// </summary>
    public TriggerCollection ViewTriggers
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                {
                    View view = db.Views[ViewName];
                    if (view != null)
                        return view.Triggers;
                    else
                        throw new InvalidDataException(ViewName);
                }
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public Index @Index
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                {
                    Table table = db.Tables[TableName];
                    if (table != null)
                        return table.Indexes[IndexName];
                    else
                        throw new InvalidDataException(TableName);
                }
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public IndexCollection Indexes
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                {
                    Table table = db.Tables[TableName];
                    if (table != null)
                        return table.Indexes;
                    else
                        throw new InvalidDataException(TableName);
                }
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public JobServer ServerJobServer
    {
        get
        {
            try
            {
                Connect();
                return server.JobServer;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public Job ServerJobServerJob
    {
        get
        {
            try
            {
                Connect();
                return server.JobServer.Jobs[JobName];
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    public JobCollection ServerJobServerJobs
    {
        get
        {
            try
            {
                Connect();
                return server.JobServer.Jobs;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// Agent Job最后一次的运行开始时间
    /// </summary>
    public DateTime ServerJobServerJobLastRunDate
    {
        get
        {
            try
            {
                Connect();
                Job job = server.JobServer.Jobs[JobName];
                if (job != null)
                    return job.LastRunDate;
                else
                    throw new InvalidDataException(JobName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// Agent Job最后一次的运行结果
    /// </summary>
    public CompletionResult ServerJobServerJobLastRunOutcome
    {
        get
        {
            try
            {
                Connect();
                Job job = server.JobServer.Jobs[JobName];
                if (job != null)
                    return job.LastRunOutcome;
                else
                    throw new InvalidDataException(JobName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 通过Windows集成认证创建本地默认实例的数据库连接
    /// </summary>
    private static void ConnectToLocalIntergated()
    {
        try
        {
            // Connecting to an instance of SQL Server using Windows Authentication
            server = new Server();   // connects to default instance
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 通过连接字符串创建数据库连接
    /// </summary>
    private static void ConnectByConnectionString()
    {
        try
        {
            //创建ServerConnection的实例
            ServerConnection connection = new ServerConnection();
            //指定连接字符串
            connection.ConnectionString = connectionString;
            //实例化Server
            server = new Server(connection);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 创建本地默认实例的数据库连接
    /// </summary>
    private static void ConnectToLocalDefaultInstance()
    {
        try
        {
            // Connecting to an instance of SQL Server using SQL Server Authentication
            server = new Server();   // connects to default instance
            server.ConnectionContext.LoginSecure = false;   // set to true for Windows Authentication
            server.ConnectionContext.Login = loginName;
            server.ConnectionContext.Password = loginPassword;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 创建数据本地命名实例的库连接
    /// </summary>
    private static void ConnectToLocalNamedInstance()
    {
        try
        {
            // Connecting to a named instance of SQL Server with SQL Server Authentication using ServerConnection
            ServerConnection srvConn = new ServerConnection();
            srvConn.ServerInstance = @".\" + instanceName;   // connects to named instance
            srvConn.LoginSecure = false;   // set to true for Windows Authentication
            srvConn.Login = loginName;
            srvConn.Password = loginPassword;
            server = new Server(srvConn);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 创建远程服务器的数据库连接
    /// </summary>
    private static void ConnectToRemoteServer()
    {
        try
        {
            // For remote connection, remote server name / ServerInstance needs to be specified
            ServerConnection srvConn2 = new ServerConnection(remoteSvrName);
            srvConn2.LoginSecure = false;
            srvConn2.Login = loginName;
            srvConn2.Password = loginPassword;
            server = new Server(srvConn2);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    private delegate void ConnectionMethods();
    ConnectionMethods[] connectionMethods =
            {
               ConnectToLocalIntergated,
               ConnectByConnectionString,
               ConnectToLocalDefaultInstance,
               ConnectToLocalNamedInstance,
               ConnectToRemoteServer
            };

    /// <summary>
    /// 连接字符串
    /// </summary>
    public static string connectionString { get; set; }

    /// <summary>
    /// 数据库名
    /// </summary>
    public string DatabaseName { get; set; }

    /// <summary>
    /// 存储过程名
    /// </summary>
    public string StoredProcedureName { get; set; }

    /// <summary>
    /// 表名
    /// </summary>
    public string TableName { get; set; }

    /// <summary>
    /// 视图名
    /// </summary>
    public string ViewName { get; set; }

    /// <summary>
    /// 索引名
    /// </summary>
    public string IndexName { get; set; }

    /// <summary>
    /// 触发器名
    /// </summary>
    public string TriggerName { get; set; }

    /// <summary>
    /// 数据的文件夹
    /// </summary>
    public string Path { get; set; }

    /// <summary>
    /// 存放备份数据的文件夹
    /// </summary>
    public string backupPath { get; set; }

    /// <summary>
    /// 数据库登录名
    /// </summary>
    public static string loginName { get; set; }

    /// <summary>
    /// Agent Job的名字
    /// </summary>
    public string JobName { get; set; }

    /// <summary>
    /// 数据库的数据占用空间大小
    /// </summary>
    public double DatabaseDataSpaceUsage
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.DataSpaceUsage;
                return -1;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 数据库的索引占用空间大小
    /// </summary>
    public double DatabaseIndexSpaceUsage
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.IndexSpaceUsage;
                return -1;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 数据库的可用空间大小
    /// </summary>
    public double DatabaseSpaceAvailable
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.SpaceAvailable;
                return -1;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 数据库的空间大小
    /// </summary>
    public string DatabasePrimaryFilePath
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.PrimaryFilePath;
                return string.Empty;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 数据库的空间大小
    /// </summary>
    public double DatabaseSize
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.Size;
                return -1;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 数据库表的数据占用空间大小
    /// </summary>
    public double TableDataSpaceUsed
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                {
                    Table table = db.Tables[TableName];
                    return table.DataSpaceUsed;
                }
                return -1;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 数据库的数据文件组
    /// </summary>
    public FileGroupCollection DatabaseFileGroups
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.FileGroups;
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 数据库的日志文件组
    /// </summary>
    public LogFileCollection DatabaseLogFiles
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                    return db.LogFiles;
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 数据库表的索引占用空间大小
    /// </summary>
    public double TableIndexSpaceUsed
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                {
                    Table table = db.Tables[TableName];
                    return table.IndexSpaceUsed;
                }
                return -1;
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 数据库表应用索引改动
    /// </summary>
    public Index IndexAlter()
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                if (table != null)
                {
                    Index index = table.Indexes[IndexName];
                    index.Alter();
                    return index;
                }
                else
                    throw new InvalidDataException(TableName);
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 数据库表索引列集
    /// </summary>
    public IndexedColumnCollection IndexedColumns
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                {
                    Table table = db.Tables[TableName];
                    if (table != null)
                    {
                        Index index = table.Indexes[IndexName];
                        return index.IndexedColumns;
                    }
                    else
                        throw new InvalidDataException(TableName);
                }
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 判断数据库表索引是否是聚簇索引
    /// 
    /// 
    /// </summary>
    public bool IndexedIsClustered
    {
        get
        {
            try
            {
                Connect();
                Database db = server.Databases[DatabaseName];
                if (db != null)
                {
                    Table table = db.Tables[TableName];
                    if (table != null)
                    {
                        Index index = table.Indexes[IndexName];
                        return index.IsClustered;
                    }
                    else
                        throw new InvalidDataException(TableName);
                }
                else
                    throw new InvalidDataException(DatabaseName);
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 数据库表重建索引
    /// </summary>
    public Index IndexRebuild()
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                if (table != null)
                {
                    Index index = table.Indexes[IndexName];
                    index.Rebuild();
                    return index;
                }
                else
                    throw new InvalidDataException(TableName);
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 数据库表重建索引
    /// </summary>
    /// <param name="partitionNumber">填充因子</param>
    /// <returns></returns>
    public Index IndexRebuild(int partitionNumber)
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                if (table != null)
                {
                    Index index = table.Indexes[IndexName];
                    index.Rebuild(partitionNumber);
                    return index;
                }
                else
                    throw new InvalidDataException(TableName);
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 数据库表索引重命名
    /// </summary>
    /// <param name="newname">新的索引名</param>
    /// <returns></returns>
    public Index IndexRename(string newname)
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                if (table != null)
                {
                    Index index = table.Indexes[IndexName];
                    index.Rename(newname);
                    return index;
                }
                else
                    throw new InvalidDataException(TableName);
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 数据库表索引重新组织
    /// </summary>
    /// <returns></returns>
    public Index IndexReorganize()
    {
        try
        {
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                Table table = db.Tables[TableName];
                if (table != null)
                {
                    Index index = table.Indexes[IndexName];
                    index.Reorganize();
                    return index;
                }
                else
                    throw new InvalidDataException(TableName);
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 登录密码
    /// </summary>
    public static string loginPassword { get; set; }

    /// <summary>
    /// 创建SQL登录名
    /// <param name="overwrite">删除已经存在的登录名</param>
    /// </summary>
    public void CreateLogin(bool overwrite = false)
    {
        try
        {
            //创建ServerConnection的实例
            ServerConnection connection = new ServerConnection();
            //指定连接字符串
            connection.ConnectionString = connectionString;
            //实例化Server
            Server server = new Server(connection);

            #region [创建数据库登录对象]
            //检查在数据库是否已经存在该登录名
            var queryLogin = from Login temp in server.Logins
                             where string.Equals(temp.Name, loginName, StringComparison.CurrentCultureIgnoreCase)
                             select temp;
            Login login = queryLogin.FirstOrDefault<Login>();
            if (login != null)
            {
                //如果存在就删除
                if (overwrite)
                    login.Drop();
                else
                    throw new InvalidDataException(loginName);
            }
            login = new Login(server, loginName);
            login.LoginType = LoginType.SqlLogin;//指定登录方式为SQL认证
            login.PasswordPolicyEnforced = true;
            login.DefaultDatabase = "master";//默认数据库
            login.Create(loginPassword);
            #endregion
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 创建数据库
    /// </summary>
    /// <param name="overwrite">删除已经存在的数据库</param>
    public Database CreateDatabase(bool overwrite = false)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (Path == null || Path.Trim() == string.Empty)
                throw new ArgumentNullException(Path);
            if (!Directory.Exists(Path))
                throw new InvalidArgumentException(Path);
            if (Path[Path.Length - 1] != '\\')
                Path += '\\';
            Connect();
            #region [创建数据库对象]
            //检查在数据库是否已经存在该数据库
            Database database = server.Databases[DatabaseName];
            if (database != null)
            {
                //如果存在就删除
                if (overwrite)
                    database.Drop();
                else
                    throw new InvalidDataException(DatabaseName);
            }

            database = new Database(server, DatabaseName);
            //指定数据库数据文件细节
            FileGroup fileGroup = new FileGroup { Name = "PRIMARY", Parent = database, IsDefault = true };
            DataFile dataFile = new DataFile
            {
                Name = DatabaseName + "_Data",
                Parent = fileGroup,
                FileName = Path + DatabaseName + ".mdf"
            };
            fileGroup.Files.Add(dataFile);
            //指定数据库日志文件细节
            LogFile logFile = new LogFile
            {
                Name = DatabaseName + "_Log",
                Parent = database,
                FileName = Path + DatabaseName + "_log.ldf"
            };

            database.FileGroups.Add(fileGroup);
            database.LogFiles.Add(logFile);

            database.Create();
            #endregion
            return server.Databases[DatabaseName];
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 删除数据库
    /// </summary>
    public void DropDatabase()
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            #region [创建数据库对象]
            //检查在数据库是否已经存在该数据库
            Database database = server.Databases[DatabaseName];
            if (database != null)
                database.Drop();
            else
                throw new InvalidDataException(DatabaseName);
            #endregion
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 备份数据库
    /// </summary>
    public Database BackupDatabase()
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (backupPath == null || backupPath.Trim() == string.Empty)
                throw new ArgumentNullException(backupPath);
            if (!Directory.Exists(backupPath))
                throw new InvalidArgumentException(backupPath);
            if (backupPath[backupPath.Length - 1] != '\\')
                backupPath += '\\';

            Connect();
            #region [创建数据库备份对象]
            Backup backup = new Backup();
            backup.Action = BackupActionType.Database;//完全备份
            backup.Database = DatabaseName;
            backup.BackupSetDescription = "Full backup of " + DatabaseName;
            backup.BackupSetName = DatabaseName + " Backup";
            //创建备份设备
            BackupDeviceItem bkDeviceItem = new BackupDeviceItem();
            bkDeviceItem.DeviceType = DeviceType.File;
            bkDeviceItem.Name = backupPath + DatabaseName + ".bak";

            backup.Devices.Add(bkDeviceItem);
            backup.Incremental = false;
            backup.LogTruncation = BackupTruncateLogType.Truncate;
            backup.SqlBackup(server);
            #endregion
            return server.Databases[DatabaseName];
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 还原数据库
    /// </summary>
    /// <param name="overwrite">是否覆盖数据库</param>
    public Database RestoreDatabase(bool overwrite = false)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (Path == null || Path.Trim() == string.Empty)
                throw new ArgumentNullException(Path);
            if (!Directory.Exists(Path))
                throw new InvalidArgumentException(Path);
            if (Path[Path.Length - 1] != '\\')
                Path += '\\';
            if (backupPath == null || backupPath.Trim() == string.Empty)
                throw new ArgumentNullException(backupPath);
            if (!Directory.Exists(backupPath))
                throw new InvalidArgumentException(backupPath);
            if (backupPath[backupPath.Length - 1] != '\\')
                backupPath += '\\';

            Connect();
            Restore restore = new Restore();
            restore.NoRecovery = false;
            restore.NoRewind = false;
            restore.ReplaceDatabase = true;
            restore.Action = RestoreActionType.Database;
            restore.Database = DatabaseName;

            Database currentDb = server.Databases[DatabaseName];

            //看是否数据库已经存在
            if (currentDb != null)
            {
                if (overwrite)
                {
                    server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
                    //Stop all processes running on the DataBase database
                    server.KillAllProcesses(DatabaseName);
                }
                else
                    throw new InvalidDataException(DatabaseName);
            }

            //创建备份设备
            BackupDeviceItem bkDeviceItem = new BackupDeviceItem();
            bkDeviceItem.DeviceType = DeviceType.File;
            bkDeviceItem.Name = backupPath + DatabaseName + ".bak";

            //如果需要重新制定Restore后的数据库的物理文件位置，需要知道数据库文件的逻辑文件名
            //可以RESTORE FILELISTONLY 来列出逻辑文件名，如果覆盖已有数据库可以通过SMO来获取
            //因本处使用的是刚刚备份的msdb数据库来Restore，所以其分别为"MSDBData"和"MSDBLog"
            //如果不指定Restore路径则默认恢复到数据库服务器存放数据的文件夹下
            RelocateFile relocateDataFile = new RelocateFile { LogicalFileName = DatabaseName + "_Data", PhysicalFileName = Path + DatabaseName + ".mdf" };
            RelocateFile relocateLogFile = new RelocateFile { LogicalFileName = DatabaseName + "_Log", PhysicalFileName = Path + DatabaseName + "_log.ldf" };

            restore.Devices.Add(bkDeviceItem);
            restore.RelocateFiles.Add(relocateDataFile);
            restore.RelocateFiles.Add(relocateLogFile);
            restore.SqlRestore(server);
            return server.Databases[DatabaseName];
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 创建数据库连接
    /// </summary>
    public void Connect()
    {
        try
        {
            if (server == null || !server.ConnectionContext.IsOpen)
            {
                if (server == null)
                    //选择连接数据库的方式
                    connectionMethods[(int)connectionType]();
                server.ConnectionContext.Connect();
                //Disable automatic disconnection. 
                server.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect;
            }
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 取消正在执行的SQL命令
    /// </summary>
    public void CancelCommand()
    {
        try
        {
            if (server.ConnectionContext.IsOpen)
                server.ConnectionContext.Cancel();
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 断开数据库连接
    /// </summary>
    public static void Disconnect()
    {
        try
        {
            if (server != null)
            {
                //Disconnect explicitly. 
                server.ConnectionContext.Disconnect();

            }
        }
        catch
        { }
    }

    /// <summary>
    /// 捕获SQL Server运行时的SQL
    /// </summary>
    public void CaptureSQL()
    {
        try
        {
            Connect();
            // Set the execution mode to CaptureSql for the connection. 
            server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;
            // Make a modification to the server that is to be captured. 
            server.UserOptions.AnsiNulls = true;
            server.Alter();
            // Iterate through the strings in the capture buffer and display the captured statements. 
            foreach (String p_s in server.ConnectionContext.CapturedSql.Text)
            {
                Console.WriteLine(p_s);
            }
            // Execute the captured statements. 
            server.ConnectionContext.ExecuteNonQuery(server.ConnectionContext.CapturedSql.Text);
            // Revert to immediate execution mode. 
            server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 获得表的Create脚本
    /// </summary>
    public string GetCreateTableScript()
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (TableName == null || TableName.Trim() == string.Empty)
                throw new ArgumentNullException(TableName);
            Connect();
            Database db = server.Databases[DatabaseName];
            Table table;
            if (db != null)
                table = db.Tables[TableName];
            else
                throw new InvalidDataException(DatabaseName);
            //初始化脚本生成器
            Scripter scripter = new Scripter(server);
            //下面这些是可选项：作用是向脚本生成器中添加需要生成的脚本内容
            //ScripOption类的属性:http://msdn.microsoft.com/zh-cn/library/microsoft.sqlserver.management.smo.scriptingoptions.aspx
            scripter.Options.Add(ScriptOption.DriAllConstraints);
            //DriAllConstraints 获取或设置布尔属性值，指定是否所有声明的参照完整性约束包含在生成的脚本。
            scripter.Options.Add(ScriptOption.DriAllKeys);
            //DriAllKeys 获取或设置布尔属性值指定的依赖关系是否生成的脚本中定义的所有声明的参照完整性密钥。
            scripter.Options.Add(ScriptOption.Default);
            //Default 获取或设置布尔属性值指定创建所引用对象是否包含在生成的脚本。
            scripter.Options.Add(ScriptOption.ContinueScriptingOnError);
            //ContinueScriptingOnError 获取或设置布尔属性值指定的脚本是否继续操作时遇到错误后。
            scripter.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
            //ConvertUserDefinedDataTypesToBaseType 获取或设置布尔属性值指定是否将用户定义的数据类型转换成最合适的SQL Server基本数据类型生成的脚本中。
            scripter.Options.Add(ScriptOption.IncludeIfNotExists);
            // IncludeIfNotExists 获取或设置一个布尔属性值，指定包括它在脚本之前，是否检查一个对象是否存在。
            scripter.Options.Add(ScriptOption.ExtendedProperties);
            //声明统一资源名称集合对象
            UrnCollection collection = new UrnCollection();
            //The UrnCollection class represents a collection of Urn objects that represent Uniform Resource Name (URN) addresses.
            collection.Add(table.Urn);

            //声明字符串集合对象：存储collection中的所有string对象（在这里其中有3个string对象）
            StringCollection sqls = scripter.Script(collection);
            StringBuilder sb = new StringBuilder();
            //遍历字符串集合对象sqls中的string对象，选择要输出的脚本语句：
            foreach (string s in sqls)
                sb.Append(s).Append("\r\nGO\r\n\r\n");
            return sb.ToString();
            //System.Collections.Specialized.StringCollection sc = tb.Script();
            //foreach (String s in sc)
            //{
            // this.richTextBox1.Text += s;
            //}

            /* ///遍历表
            foreach( var item in db.Tables ) {
            //sb.AppendLine( item.ToString() );
            }

            //遍历字段
            foreach( var item in tb.Columns ) {
            //sb.AppendLine(item.ToString());
            }

            //遍历索引
            foreach( var item in tb.Indexes ) {
            //sb.AppendLine(item.ToString());
            }

            //遍历触发器
            foreach( var item in tb.Triggers ) {
            sb.AppendLine( item.ToString() );
            }*/
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 获得视图的Create脚本
    /// </summary>
    public string GetCreateViewScript()
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (ViewName == null || ViewName.Trim() == string.Empty)
                throw new ArgumentNullException(ViewName);
            Connect();
            Database db = server.Databases[DatabaseName];
            View view;
            if (db != null)
                view = db.Views[ViewName];
            else
                throw new InvalidDataException(DatabaseName);
            //初始化脚本生成器
            Scripter scripter = new Scripter(server);
            //下面这些是可选项：作用是向脚本生成器中添加需要生成的脚本内容
            //ScripOption类的属性:http://msdn.microsoft.com/zh-cn/library/microsoft.sqlserver.management.smo.scriptingoptions.aspx
            scripter.Options.Add(ScriptOption.DriAllConstraints);
            //DriAllConstraints 获取或设置布尔属性值，指定是否所有声明的参照完整性约束包含在生成的脚本。
            scripter.Options.Add(ScriptOption.DriAllKeys);
            //DriAllKeys 获取或设置布尔属性值指定的依赖关系是否生成的脚本中定义的所有声明的参照完整性密钥。
            scripter.Options.Add(ScriptOption.Default);
            //Default 获取或设置布尔属性值指定创建所引用对象是否包含在生成的脚本。
            scripter.Options.Add(ScriptOption.ContinueScriptingOnError);
            //ContinueScriptingOnError 获取或设置布尔属性值指定的脚本是否继续操作时遇到错误后。
            scripter.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
            //ConvertUserDefinedDataTypesToBaseType 获取或设置布尔属性值指定是否将用户定义的数据类型转换成最合适的SQL Server基本数据类型生成的脚本中。
            scripter.Options.Add(ScriptOption.IncludeIfNotExists);
            // IncludeIfNotExists 获取或设置一个布尔属性值，指定包括它在脚本之前，是否检查一个对象是否存在。
            scripter.Options.Add(ScriptOption.ExtendedProperties);
            //声明统一资源名称集合对象
            UrnCollection collection = new UrnCollection();
            //The UrnCollection class represents a collection of Urn objects that represent Uniform Resource Name (URN) addresses.
            collection.Add(view.Urn);

            //声明字符串集合对象：存储collection中的所有string对象（在这里其中有3个string对象）
            StringCollection sqls = scripter.Script(collection);
            StringBuilder sb = new StringBuilder();
            //遍历字符串集合对象sqls中的string对象，选择要输出的脚本语句：
            foreach (string s in sqls)
                sb.Append(s).Append("\r\nGO\r\n\r\n");
            return sb.ToString();
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 获得数据库中所有视图的Create脚本
    /// </summary>
    public Dictionary<string, string> GetCreateViewScripts(bool containsSystemObject = false)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db == null)
                throw new InvalidDataException(DatabaseName);
            //初始化脚本生成器
            Scripter scripter = new Scripter(server);
            //下面这些是可选项：作用是向脚本生成器中添加需要生成的脚本内容
            //ScripOption类的属性:http://msdn.microsoft.com/zh-cn/library/microsoft.sqlserver.management.smo.scriptingoptions.aspx
            scripter.Options.Add(ScriptOption.DriAllConstraints);
            //DriAllConstraints 获取或设置布尔属性值，指定是否所有声明的参照完整性约束包含在生成的脚本。
            scripter.Options.Add(ScriptOption.DriAllKeys);
            //DriAllKeys 获取或设置布尔属性值指定的依赖关系是否生成的脚本中定义的所有声明的参照完整性密钥。
            scripter.Options.Add(ScriptOption.Default);
            //Default 获取或设置布尔属性值指定创建所引用对象是否包含在生成的脚本。
            scripter.Options.Add(ScriptOption.ContinueScriptingOnError);
            //ContinueScriptingOnError 获取或设置布尔属性值指定的脚本是否继续操作时遇到错误后。
            scripter.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
            //ConvertUserDefinedDataTypesToBaseType 获取或设置布尔属性值指定是否将用户定义的数据类型转换成最合适的SQL Server基本数据类型生成的脚本中。
            scripter.Options.Add(ScriptOption.IncludeIfNotExists);
            // IncludeIfNotExists 获取或设置一个布尔属性值，指定包括它在脚本之前，是否检查一个对象是否存在。
            scripter.Options.Add(ScriptOption.ExtendedProperties);
            Dictionary<string, string> viewSqls = new Dictionary<string, string>();
            if (containsSystemObject)
            {
                foreach (View view in db.Views)
                {
                    //声明统一资源名称集合对象
                    UrnCollection collection = new UrnCollection();
                    //The UrnCollection class represents a collection of Urn objects that represent Uniform Resource Name (URN) addresses.
                    collection.Add(view.Urn);
                    //声明字符串集合对象：存储collection中的所有string对象（在这里其中有3个string对象）
                    StringCollection sqls = scripter.Script(collection);
                    StringBuilder sb = new StringBuilder();
                    //遍历字符串集合对象sqls中的string对象，选择要输出的脚本语句：
                    foreach (string s in sqls)
                        sb.Append(s).Append("\r\nGO\r\n\r\n");
                    viewSqls.Add(view.Name, sb.ToString());
                }
            }
            else
            {
                DataTable views = db.ExecuteWithResults("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS").Tables[0];
                foreach (DataRow row in views.Rows)
                {
                    View view = db.Views[(string)row["TABLE_NAME"]];
                    //声明统一资源名称集合对象
                    UrnCollection collection = new UrnCollection();
                    //The UrnCollection class represents a collection of Urn objects that represent Uniform Resource Name (URN) addresses.
                    collection.Add(view.Urn);
                    //声明字符串集合对象：存储collection中的所有string对象（在这里其中有3个string对象）
                    StringCollection sqls = scripter.Script(collection);
                    StringBuilder sb = new StringBuilder();
                    //遍历字符串集合对象sqls中的string对象，选择要输出的脚本语句：
                    foreach (string s in sqls)
                        sb.Append(s).Append("\r\nGO\r\n\r\n");
                    viewSqls.Add(view.Name, sb.ToString());
                }
            }
            return viewSqls;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 视图的创建脚本
    /// </summary>
    public string ViewCreationScript
    {
        get
        {
            try
            {
                if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                    throw new ArgumentNullException(DatabaseName);
                if (ViewName == null || ViewName.Trim() == string.Empty)
                    throw new ArgumentNullException(ViewName);
                Connect();
                Database db = server.Databases[DatabaseName];
                View view;
                if (db != null)
                    view = db.Views[ViewName];
                else
                    throw new InvalidDataException(DatabaseName);
                //初始化脚本生成器
                Scripter scripter = new Scripter(server);
                //下面这些是可选项：作用是向脚本生成器中添加需要生成的脚本内容
                //Default 获取或设置布尔属性值指定创建所引用对象是否包含在生成的脚本。
                scripter.Options.Add(ScriptOption.ContinueScriptingOnError);
                //ContinueScriptingOnError 获取或设置布尔属性值指定的脚本是否继续操作时遇到错误后。
                scripter.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
                //ConvertUserDefinedDataTypesToBaseType 获取或设置布尔属性值指定是否将用户定义的数据类型转换成最合适的SQL Server基本数据类型生成的脚本中。
                // IncludeIfNotExists 获取或设置一个布尔属性值，指定包括它在脚本之前，是否检查一个对象是否存在。
                scripter.Options.Add(ScriptOption.ExtendedProperties);
                //声明统一资源名称集合对象
                UrnCollection collection = new UrnCollection();
                //The UrnCollection class represents a collection of Urn objects that represent Uniform Resource Name (URN) addresses.
                collection.Add(view.Urn);

                //声明字符串集合对象：存储collection中的所有string对象（在这里其中有3个string对象）
                StringCollection sqls = scripter.Script(collection);
                StringBuilder sb = new StringBuilder();
                //遍历字符串集合对象sqls中的string对象，选择要输出的脚本语句：
                foreach (string s in sqls)
                    sb.Append(s).Append("\r\nGO\r\n\r\n");
                return sb.ToString();
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 存储过程的创建脚本
    /// </summary>
    public string StoredProcedureCreationScript
    {
        get
        {
            try
            {
                if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                    throw new ArgumentNullException(DatabaseName);
                if (StoredProcedureName == null || StoredProcedureName.Trim() == string.Empty)
                    throw new ArgumentNullException(StoredProcedureName);
                Connect();
                Database db = server.Databases[DatabaseName];
                StoredProcedure SP;
                if (db != null)
                    SP = db.StoredProcedures[StoredProcedureName];
                else
                    throw new InvalidDataException(DatabaseName);
                //初始化脚本生成器
                Scripter scripter = new Scripter(server);
                //下面这些是可选项：作用是向脚本生成器中添加需要生成的脚本内容
                //Default 获取或设置布尔属性值指定创建所引用对象是否包含在生成的脚本。
                scripter.Options.Add(ScriptOption.ContinueScriptingOnError);
                //ContinueScriptingOnError 获取或设置布尔属性值指定的脚本是否继续操作时遇到错误后。
                scripter.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
                //ConvertUserDefinedDataTypesToBaseType 获取或设置布尔属性值指定是否将用户定义的数据类型转换成最合适的SQL Server基本数据类型生成的脚本中。
                // IncludeIfNotExists 获取或设置一个布尔属性值，指定包括它在脚本之前，是否检查一个对象是否存在。
                scripter.Options.Add(ScriptOption.ExtendedProperties);
                //声明统一资源名称集合对象
                UrnCollection collection = new UrnCollection();
                //The UrnCollection class represents a collection of Urn objects that represent Uniform Resource Name (URN) addresses.
                collection.Add(SP.Urn);

                //声明字符串集合对象：存储collection中的所有string对象（在这里其中有3个string对象）
                StringCollection sqls = scripter.Script(collection);
                StringBuilder sb = new StringBuilder();
                //遍历字符串集合对象sqls中的string对象，选择要输出的脚本语句：
                foreach (string s in sqls)
                    sb.Append(s).Append("\r\nGO\r\n\r\n");
                return sb.ToString();
            }
            catch (Exception ex)
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 获得存储过程的Create脚本
    /// </summary>
    public string GetCreateSPScript()
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (StoredProcedureName == null || StoredProcedureName.Trim() == string.Empty)
                throw new ArgumentNullException(StoredProcedureName);
            Connect();
            Database db = server.Databases[DatabaseName];
            StoredProcedure SP;
            if (db != null)
                SP = db.StoredProcedures[StoredProcedureName];
            else
                throw new InvalidDataException(DatabaseName);
            //初始化脚本生成器
            Scripter scripter = new Scripter(server);
            //下面这些是可选项：作用是向脚本生成器中添加需要生成的脚本内容
            //ScripOption类的属性:http://msdn.microsoft.com/zh-cn/library/microsoft.sqlserver.management.smo.scriptingoptions.aspx
            scripter.Options.Add(ScriptOption.DriAllConstraints);
            //DriAllConstraints 获取或设置布尔属性值，指定是否所有声明的参照完整性约束包含在生成的脚本。
            scripter.Options.Add(ScriptOption.DriAllKeys);
            //DriAllKeys 获取或设置布尔属性值指定的依赖关系是否生成的脚本中定义的所有声明的参照完整性密钥。
            scripter.Options.Add(ScriptOption.Default);
            //Default 获取或设置布尔属性值指定创建所引用对象是否包含在生成的脚本。
            scripter.Options.Add(ScriptOption.ContinueScriptingOnError);
            //ContinueScriptingOnError 获取或设置布尔属性值指定的脚本是否继续操作时遇到错误后。
            scripter.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
            //ConvertUserDefinedDataTypesToBaseType 获取或设置布尔属性值指定是否将用户定义的数据类型转换成最合适的SQL Server基本数据类型生成的脚本中。
            scripter.Options.Add(ScriptOption.IncludeIfNotExists);
            // IncludeIfNotExists 获取或设置一个布尔属性值，指定包括它在脚本之前，是否检查一个对象是否存在。
            scripter.Options.Add(ScriptOption.ExtendedProperties);
            //声明统一资源名称集合对象
            UrnCollection collection = new UrnCollection();
            //The UrnCollection class represents a collection of Urn objects that represent Uniform Resource Name (URN) addresses.
            collection.Add(SP.Urn);

            //声明字符串集合对象：存储collection中的所有string对象（在这里其中有3个string对象）
            StringCollection sqls = scripter.Script(collection);
            StringBuilder sb = new StringBuilder();
            //遍历字符串集合对象sqls中的string对象，选择要输出的脚本语句：
            foreach (string s in sqls)
                sb.Append(s).Append("\r\nGO\r\n\r\n");
            return sb.ToString();
            //System.Collections.Specialized.StringCollection sc = tb.Script();
            //foreach (String s in sc)
            //{
            // this.richTextBox1.Text += s;
            //}

            /* ///遍历表
            foreach( var item in db.Tables ) {
            //sb.AppendLine( item.ToString() );
            }

            //遍历字段
            foreach( var item in tb.Columns ) {
            //sb.AppendLine(item.ToString());
            }

            //遍历索引
            foreach( var item in tb.Indexes ) {
            //sb.AppendLine(item.ToString());
            }

            //遍历触发器
            foreach( var item in tb.Triggers ) {
            sb.AppendLine( item.ToString() );
            }*/
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 遍历数据表依赖的数据库对象
    /// </summary>
    /// <returns></returns>
    public List<string> WalkDependency()
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (TableName == null || TableName.Trim() == string.Empty)
                throw new ArgumentNullException(TableName);
            Connect();
            List<string> ls = new List<string>();
            // Reference the database.  
            Database db = server.Databases[DatabaseName];
            Table tb = db.Tables[TableName];

            // Define a Scripter object and set the required scripting options. 
            Scripter scrp = new Scripter(server);
            scrp.Options.ScriptDrops = false;
            scrp.Options.WithDependencies = true;
            scrp.Options.Indexes = true;   // To include indexes
            scrp.Options.DriAllConstraints = true;   // to include referential constraints in the script

            // check if the table is not a system table
            if (tb.IsSystemObject == false)
            {
                // Generating script for table tb
                StringCollection sc = scrp.Script(new Urn[] { tb.Urn });
                foreach (string st in sc)
                    ls.Add(st);
            }
            return ls;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 本地复制数据库
    /// </summary>
    /// <param name="distinationDBName">复制到的数据库名</param>
    public Database CopyDBonLocal(string distinationDBName)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (TableName == null || TableName.Trim() == string.Empty)
                throw new ArgumentNullException(TableName);
            Connect();
            //Reference the AdventureWorks2012 database 
            Database db;
            db = server.Databases[DatabaseName];
            //Create a new database that is to be destination database. 
            Database dbCopy;
            dbCopy = new Database(server, distinationDBName);
            dbCopy.Create();
            //Define a Transfer object and set the required options and properties. 
            Transfer xfr;
            xfr = new Transfer(db);
            xfr.CopyAllTables = true;
            xfr.Options.WithDependencies = true;
            xfr.Options.ContinueScriptingOnError = true;
            xfr.DestinationDatabase = distinationDBName;
            xfr.DestinationServer = server.Name;
            xfr.DestinationLoginSecure = true;
            xfr.CopySchema = true;
            //Script the transfer. Alternatively perform immediate data transfer 
            // with TransferData method. 
            xfr.ScriptTransfer();
            return db;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 执行SQL脚本
    /// </summary>
    /// <param name="script">SQL脚本</param>
    public void ExecuteNonQuery(string script)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            if (server.ConnectionContext.SqlConnectionObject.Database != DatabaseName)
                server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
            server.ConnectionContext.ExecuteNonQuery(script);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 查询单个数据的方法
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param">参数数组（可选）</param>
    /// <returns></returns>
    public object ExecuteScalar(string sql, params SqlParameter[] param)
    {
        SqlCommand cmd = null;
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            if (server.ConnectionContext.SqlConnectionObject.Database != DatabaseName)
                server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
            SqlConnection cn = server.ConnectionContext.SqlConnectionObject;
            cn.Open();
            cmd = new SqlCommand(sql);
            if (param.Length > 0)
            {
                foreach (SqlParameter p in param)
                    cmd.Parameters.Add(p);
            }
            cmd.Connection = cn;
            return cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            if (cmd != null)
                cmd.Dispose();
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 查询数据库返回DataTable对象的方法
    /// </summary>
    /// <param name="sql">要执行的SQL语句</param>
    /// <param name="param">参数数组（可选）</param>
    /// <returns></returns>
    public DataTable GetDataTable(string sql, params SqlParameter[] param)
    {
        SqlDataAdapter dapt = null;
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            if (server.ConnectionContext.SqlConnectionObject.Database != DatabaseName)
                server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
            SqlConnection cn = server.ConnectionContext.SqlConnectionObject;
            dapt = new SqlDataAdapter(sql, cn);
            if (param.Length > 0)
            {
                foreach (SqlParameter p in param)
                {
                    if (p != null)
                        dapt.SelectCommand.Parameters.Add(p);
                }
            }
            DataTable dt = new DataTable();
            dapt.Fill(dt);
            dapt.Dispose();
            return dt;
        }
        catch (Exception ex)
        {
            if (dapt != null)
                dapt.Dispose();
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 查询数据库返回DataSet对象的方法
    /// </summary>
    /// <param name="sql">要执行的SQL语句</param>
    /// <param name="param">参数数组（可选）</param>
    /// <returns></returns>
    public DataSet GetDataSet(string sql, params SqlParameter[] param)
    {
        SqlDataAdapter dapt = null;
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            if (server.ConnectionContext.SqlConnectionObject.Database != DatabaseName)
                server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
            SqlConnection cn = server.ConnectionContext.SqlConnectionObject;
            dapt = new SqlDataAdapter(sql, cn);

            if (param.Length > 0)
            {
                foreach (SqlParameter p in param)
                {
                    if (p != null)
                        dapt.SelectCommand.Parameters.Add(p);
                }
            }
            DataSet ds = new DataSet();
            dapt.Fill(ds);
            dapt.Dispose();
            return ds;
        }
        catch (Exception ex)
        {
            if (dapt != null)
                dapt.Dispose();
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 执行SQL语句，实现数据库事务
    /// </summary>
    /// <param name="executeableUnits">SQL语句和参数组成的可执行单元</param>
    /// <returns></returns>
    public int ExecuteSqlTransaction(SMO.ExecuteableUnit executeableUnit)
    {
        SqlTransaction trans = null;
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            if (server.ConnectionContext.SqlConnectionObject.Database != DatabaseName)
                server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
            SqlConnection cn = server.ConnectionContext.SqlConnectionObject;
            cn.Open();
            SqlCommand comm = cn.CreateCommand();
            trans = cn.BeginTransaction();
            comm.Connection = cn;
            comm.Transaction = trans;
            comm.Parameters.Clear();
            comm.CommandText = executeableUnit.SQL;
            if (executeableUnit.param.Length > 0)
            {
                foreach (SqlParameter p in executeableUnit.param)
                    comm.Parameters.Add(p);
            }
            int result = comm.ExecuteNonQuery();
            trans.Commit();
            trans.Dispose();
            return result;
        }
        catch (Exception ex)
        {
            try
            {
                trans.Rollback();
                trans.Dispose();
            }
            catch (Exception ex2)
            {
                throw ex2;
            }
            throw ex;
        }
    }

    /// <summary>
    /// 执行多条SQL语句，实现数据库事务
    /// </summary>
    /// <param name="executeableUnits">SQL语句和参数组成的可执行单元组</param>
    /// <returns></returns>
    public int ExecuteSqlTransaction(params SMO.ExecuteableUnit[] executeableUnits)
    {
        SqlTransaction trans = null;
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            if (server.ConnectionContext.SqlConnectionObject.Database != DatabaseName)
                server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
            SqlConnection cn = server.ConnectionContext.SqlConnectionObject;
            cn.Open();
            SqlCommand comm = cn.CreateCommand();
            trans = cn.BeginTransaction();
            comm.Connection = cn;
            comm.Transaction = trans;
            int result = 0;
            foreach (SMO.ExecuteableUnit exeUnit in executeableUnits)
            {
                comm.Parameters.Clear();
                comm.CommandText = exeUnit.SQL;
                if (exeUnit.param.Length > 0)
                {
                    foreach (SqlParameter p in exeUnit.param)
                        comm.Parameters.Add(p);
                }
                result += comm.ExecuteNonQuery();
            }
            trans.Commit();
            trans.Dispose();
            return result;
        }
        catch (Exception ex)
        {
            try
            {
                trans.Rollback();
                trans.Dispose();
            }
            catch (Exception ex2)
            {
                throw ex2;
            }
            throw ex;
        }
    }

    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="strStoreProcName">存储过程名称</param>
    /// <param name="strParametersName">参数名集合</param>
    /// <param name="strParameterValue">参数值集合</param>
    /// <returns>执行状态</returns>
    public bool ExecuteStoreProcedure(string[] strParametersName, string[] strParameterValue)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (StoredProcedureName == null || StoredProcedureName.Trim() == string.Empty)
                throw new ArgumentNullException(StoredProcedureName);
            Connect();
            if (server.ConnectionContext.SqlConnectionObject.Database != DatabaseName)
                server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
            using (SqlCommand objSqlCmd = new SqlCommand(StoredProcedureName, server.ConnectionContext.SqlConnectionObject))
            {
                objSqlCmd.CommandType = CommandType.StoredProcedure;
                int intParaCount = strParametersName.Length;
                for (int i = 0; i < intParaCount; i++)
                {
                    objSqlCmd.Parameters.Add(strParametersName[i], SqlDbType.NVarChar);
                    objSqlCmd.Parameters[strParametersName[i]].Value = strParameterValue[i];
                }
                objSqlCmd.ExecuteNonQuery();
            }
            return true;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 执行SQL脚本文件
    /// </summary>
    /// <param name="checkDBExists">检查数据库是否存在</param>
    /// <param name="scriptFile">SQL脚本文件</param>
    public void ExecuteSQLFile(string scriptFile, bool checkDBExists = true)
    {
        try
        {
            if (checkDBExists && (DatabaseName == null || DatabaseName.Trim() == string.Empty))
                throw new ArgumentNullException(DatabaseName);
            if (scriptFile == null || scriptFile.Trim() == string.Empty)
                throw new ArgumentNullException(scriptFile);
            if (!File.Exists(scriptFile))
                throw new InvalidArgumentException(scriptFile);
            Connect();
            if (checkDBExists && server.ConnectionContext.SqlConnectionObject.Database != DatabaseName)
                server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
            string script = File.ReadAllText(scriptFile);
            server.ConnectionContext.ExecuteNonQuery(script);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 执行数据快速插入或复制,要求数据源DataTable的表结构与目标表完全一致
    /// </summary>
    ///<param name = "dtSource">数据源DataTable</param>
    ///<param name = "strDestinationTableName">目标表名称</param>
    ///<returns>返回执行状态</returns>
    public bool ExecuteSqlBulkCopy(DataTable dtSource, string strDestinationTableName)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            if (server.ConnectionContext.SqlConnectionObject.Database != DatabaseName)
                server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
            using (SqlBulkCopy objSqlBulkCopy = new SqlBulkCopy(server.ConnectionContext.SqlConnectionObject))
            {
                objSqlBulkCopy.DestinationTableName = strDestinationTableName;
                for (int i = 0; i < dtSource.Columns.Count; i++)
                    objSqlBulkCopy.ColumnMappings.Add(i, i);
                objSqlBulkCopy.WriteToServer(dtSource);
            }
            return true;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 将DataGridView的数据源更新到数据库中
    /// </summary>
    /// <param name="dtResult">数据源</param>
    /// <returns>执行状态</returns>
    public void UpdateDataSource(DataTable dtResult, string strDestinationTableName)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (strDestinationTableName == null || strDestinationTableName.Trim() == string.Empty)
                throw new ArgumentNullException(strDestinationTableName);
            Connect();
            if (server.ConnectionContext.SqlConnectionObject.Database != DatabaseName)
                server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
            StringBuilder sqlScript = new StringBuilder();
            sqlScript.AppendFormat("TRUNCATE TABLE {0}", strDestinationTableName);
            ExecuteNonQuery(sqlScript.ToString());
            ExecuteSqlBulkCopy(dtResult, strDestinationTableName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 清空数据表
    /// </summary>
    /// <param name="tableNames">数据表数组</param>
    public void TrancateData(params String[] tableNames)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            if (server.ConnectionContext.SqlConnectionObject.Database != DatabaseName)
                server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
            StringBuilder sqlScript = new StringBuilder();
            foreach (String tableName in tableNames)
            {
                sqlScript.AppendFormat("TRUNCATE TABLE {0}", tableName);
                sqlScript.AppendLine();
            }
            ExecuteNonQuery(sqlScript.ToString());
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 清空数据库中所有的表
    /// </summary>
    public Database TruncateDatabase()
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            Database db = new Database(server, DatabaseName);
            if (db == null)
                throw new Exception("Specified Database not found the server " + server.Name);
            List<String> tables = new List<string>();
            foreach (Table dataTable in db.Tables)
                tables.Add(dataTable.Name);
            TrancateData(tables.ToArray());
            return db;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 数据库复制删除
    /// </summary>
    /// <param name="destinationDataBase">目标数据库</param>
    public Database CopyDelDB(string destinationDataBase)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (destinationDataBase == null || destinationDataBase.Trim() == string.Empty)
                throw new ArgumentNullException(destinationDataBase);
            Connect();
            Database dbSource = server.Databases[DatabaseName];
            //看是否数据库已经存在
            if (dbSource != null)
            {
                Database dbDestination = server.Databases[destinationDataBase];

                if (dbDestination == null || dbSource == null)
                    throw new Exception("Specified Database not found the server " + server.Name);
                StringBuilder sqlScript = new StringBuilder("");
                sqlScript.AppendLine("USE " + destinationDataBase + ";");

                foreach (Table dataTable in dbSource.Tables)
                {
                    if (!dbDestination.Tables.Contains(dataTable.Name, dataTable.Schema))
                        continue;
                    sqlScript.AppendFormat("INSERT INTO {0} \n SELECT * FROM {0}", dataTable.Name);
                    sqlScript.AppendLine();
                }
                dbDestination.ExecuteNonQuery(sqlScript.ToString());
                return dbSource;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 效验数据库备份文件
    /// </summary>
    /// <param name="Message">效验结果消息</param>
    /// <returns>效验结论</returns>
    public bool VerifyingBackups(out string Message)
    {
        try
        {
            if (!Directory.Exists(backupPath))
                throw new InvalidArgumentException(backupPath);
            if (backupPath[backupPath.Length - 1] != '\\')
                backupPath += '\\';
            Connect();
            Restore res = new Restore();
            res.Devices.AddDevice(backupPath + DatabaseName + ".bak", DeviceType.File);
            return res.SqlVerify(server, out Message);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 分离数据库
    /// </summary>
    public void DetachDatabase()
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            //test为数据库的名称
            Database currentDb = server.Databases[DatabaseName];

            //看是否数据库已经存在
            if (currentDb != null)
            {
                server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
                server.KillAllProcesses(DatabaseName);
                currentDb.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
                currentDb.Alter(TerminationClause.RollbackTransactionsImmediately);
                server.DetachDatabase(DatabaseName, true);
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 附加数据库
    /// 注意:如果数据库已经存在，附加将失败
    /// </summary>
    /// <param name="owner">数据库所有者</param>
    public Database AttachDatabase(string owner)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            if (Path == null || Path.Trim() == string.Empty)
                throw new ArgumentNullException(Path);
            if (!Directory.Exists(Path))
                throw new InvalidArgumentException(Path);
            if (Path[Path.Length - 1] != '\\')
                Path += '\\';

            StringCollection files = new StringCollection();
            Database currentDb = server.Databases[DatabaseName];
            if (currentDb == null)
            {
                //主文件是必须的
                //文件名、文件后缀的大小写无所谓,即实际文件是Over.Mdf但这里指定成over.mdf也没有关系
                files.Add(Path + DatabaseName + ".mdf");
                //log可以不设置string.Format(@"{0}\{1}_Log.ldf", Path, databaseName)
                files.Add(Path + DatabaseName + "_log.ldf");
                //AttachDatabase(数据库名称,附加数据库文件路径,数据库所有者,AttachOptions选项)
                //第三个设置了不起作用！附加后的数据库显示所有者为Connection中连接用户为所有者tj
                server.AttachDatabase(DatabaseName, files, owner, AttachOptions.None);
                return server.Databases[DatabaseName];
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 收缩数据库
    /// </summary>
    public Database DatabaseShrink()
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                //Shrink the database without truncating the log.
                db.Shrink(20, ShrinkMethod.NoTruncate);

                //Truncate the log.
                db.TruncateLog();
                return db;
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 更新数据库索引统计信息
    /// </summary>
    public Database DatabaseUpdateIndexStatistics()
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
                db.UpdateIndexStatistics();
            else
                throw new InvalidDataException(DatabaseName);
            return db;
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 在数据库的所有表的所有字符类型字段中搜索一个关键字值，并返回所在的数据库的字段和值
    /// </summary>
    /// <param name="keyword">关键字</param>
    /// <param name="withDataType">是否带数据类型</param>
    /// <param name="isExact">是否精确搜索</param>
    /// <returns></returns>
    public DataTable DatabaseSearchString(string keyword, bool withDataType = false, bool isExact = false)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (keyword.Trim() == string.Empty)
                return null;
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                return db.ExecuteWithResults(
                    string.Format(
                    @"  DECLARE @Results AS TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	                    SET NOCOUNT ON

	                    DECLARE @TableName nvarchar(256), @TableName2 nvarchar(258), @ColumnName nvarchar(128), @ColumnName2 nvarchar(130), @SearchStr2 nvarchar(110)
	                    SET  @TableName = ''
	                    SET @SearchStr2 = QUOTENAME('{0}','''')

	                    WHILE @TableName IS NOT NULL
	                    BEGIN
		                    SET @ColumnName = ''
		                    SET @TableName = 
		                    (
			                    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			                    FROM 	INFORMATION_SCHEMA.TABLES
			                    WHERE 		TABLE_TYPE = 'BASE TABLE'
				                    AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				                    AND	OBJECTPROPERTY(
						                    OBJECT_ID(
							                    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							                        ), 'IsMSShipped'
						                            ) = 0
		                    )

		                    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		                    BEGIN
			                    SET @ColumnName =
			                    (
				                    SELECT MIN(QUOTENAME(COLUMN_NAME))
				                    FROM 	INFORMATION_SCHEMA.COLUMNS
				                    WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					                    AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					                    AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					                    AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			                    )

                                SET @TableName2 =  REPLACE(@TableName,'''','''''')
 
			                    IF @ColumnName IS NOT NULL
			                    BEGIN
                                    SET @ColumnName2 =  REPLACE(@ColumnName,'''','''''')
				                    INSERT INTO @Results
				                    EXEC
				                    (
					                    'SELECT TOP 1 ''' + @TableName2 + '.' + @ColumnName2 + ''', LEFT(' + @ColumnName + ', 3630) 
					                    FROM ' + @TableName + ' (NOLOCK) ' +
					                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				                    )
			                    END
		                    END	
	                    END

                        " + (withDataType ?
                          @"SELECT ColumnName, DATA_TYPE + 
                            CASE WHEN [CHARACTER_MAXIMUM_LENGTH] IS NULL THEN
                              CASE WHEN [DATA_TYPE] = 'decimal' THEN
                                '(' + CAST([NUMERIC_PRECISION] AS VARCHAR) + ',' + CAST([NUMERIC_SCALE] AS VARCHAR) + ')'
                              ELSE
                                ''
                              END
                            ELSE
                              '(' +
                              CASE WHEN [CHARACTER_MAXIMUM_LENGTH] = -1 THEN
                                'MAX'
                              ELSE
                                CAST([CHARACTER_MAXIMUM_LENGTH] AS VARCHAR)
                              END +
                              ')'
                            END [ColumnType], ColumnValue
                            FROM @Results R
                            INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON QUOTENAME(C.[TABLE_SCHEMA]) + '.' + QUOTENAME(C.[TABLE_NAME]) + '.' + QUOTENAME(C.[COLUMN_NAME]) = ColumnName
                            ORDER BY ColumnName"
                          :
                          "SELECT ColumnName, ColumnValue FROM @Results ORDER BY ColumnName")
                    , (isExact ? string.Empty : "%") + keyword.Trim().Replace("'", "''") + (isExact ? string.Empty : "%"))
                    ).Tables[0];
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 在数据库的所有表的所有字符类型字段中搜索多个关键字值，并返回所在的数据库的字段和值
    /// </summary>
    /// <param name="keyword">关键字</param>
    /// <param name="withDataType">是否带数据类型</param>
    /// <param name="isExact">是否精确搜索</param>
    /// <returns></returns>
    public DataTable DatabaseSearchString(string[] keywords, bool withDataType = false, bool isExact = false)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (keywords == null)
                return null;
            List<string> lsKeywords = new List<string>(keywords);
            List<string> lsKeywords2 = new List<string>();
            lsKeywords.ForEach((keyword) =>
            {
                if (keyword.Trim() != string.Empty)
                    lsKeywords2.Add("'''" + (isExact ? string.Empty : "%") + keyword.Trim().Replace("'", "''") + (isExact ? string.Empty : "%") + "'''");
            }
            );
            if (lsKeywords2.Count == 0)
                return null;
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                string sql =
                    string.Format(
                    @"  DECLARE @Results AS TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	                    SET NOCOUNT ON

	                    DECLARE @TableName nvarchar(256), @TableName2 nvarchar(258), @ColumnName nvarchar(128), @ColumnName2 nvarchar(130), @SearchStr2 nvarchar(110)
	                    SET  @TableName = ''

	                    WHILE @TableName IS NOT NULL
	                    BEGIN
		                    SET @ColumnName = ''
		                    SET @TableName = 
		                    (
			                    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			                    FROM 	INFORMATION_SCHEMA.TABLES
			                    WHERE 		TABLE_TYPE = 'BASE TABLE'
				                    AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				                    AND	OBJECTPROPERTY(
						                    OBJECT_ID(
							                    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							                        ), 'IsMSShipped'
						                            ) = 0
		                    )

		                    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		                    BEGIN
			                    SET @ColumnName =
			                    (
				                    SELECT MIN(QUOTENAME(COLUMN_NAME))
				                    FROM 	INFORMATION_SCHEMA.COLUMNS
				                    WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					                    AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					                    AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					                    AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			                    )

                                SET @TableName2 =  REPLACE(@TableName,'''','''''')
	
			                    IF @ColumnName IS NOT NULL
			                    BEGIN
                                    SET @ColumnName2 =  REPLACE(@ColumnName,'''','''''')
				                    INSERT INTO @Results
				                    EXEC
				                    (
					                    'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @ColumnName + ' LIKE ' + {0} + ') SELECT DISTINCT ''' + @TableName2 + '.' + @ColumnName2 + ''', LEFT(' + @ColumnName + ', 3630) 
					                    FROM ' + @TableName + ' (NOLOCK) WHERE ' + @ColumnName + ' LIKE ' + {0}
				                    )
			                    END
		                    END	
	                    END

                        " + (withDataType ?
                          @"SELECT ColumnName, DATA_TYPE + 
                            CASE WHEN [CHARACTER_MAXIMUM_LENGTH] IS NULL THEN
                              CASE WHEN [DATA_TYPE] = 'decimal' THEN
                                '(' + CAST([NUMERIC_PRECISION] AS VARCHAR) + ',' + CAST([NUMERIC_SCALE] AS VARCHAR) + ')'
                              ELSE
                                ''
                              END
                            ELSE
                              '(' +
                              CASE WHEN [CHARACTER_MAXIMUM_LENGTH] = -1 THEN
                                'MAX'
                              ELSE
                                CAST([CHARACTER_MAXIMUM_LENGTH] AS VARCHAR)
                              END +
                              ')'
                            END [ColumnType], ColumnValue
                            FROM @Results R
                            INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON QUOTENAME(C.[TABLE_SCHEMA]) + '.' + QUOTENAME(C.[TABLE_NAME]) + '.' + QUOTENAME(C.[COLUMN_NAME]) = ColumnName
                            ORDER BY ColumnName"
                          :
                          "SELECT ColumnName, ColumnValue FROM @Results ORDER BY ColumnName")
                    , lsKeywords2.Aggregate((current, next) => current + @" + ' OR ' + @ColumnName + ' LIKE ' + " + next));
                return db.ExecuteWithResults(sql).Tables[0];
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            if (ex.HResult == -2146233088)
                return null;
            else
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 在数据库的所有表的所有字符类型字段中搜索多个关键字值，并返回所在的数据库的字段和值
    /// </summary>
    /// <param name="keyword">关键字</param>
    /// <param name="withDataType">是否带数据类型</param>
    /// <param name="isExact">是否精确搜索</param>
    /// <returns></returns>
    public DataTable DatabaseSearchValue(List<object> lsValues, bool withDataType = false, bool isExact = false)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (lsValues.Count == 0)
                return null;
            List<string> lsKeywords = new List<string>();
            List<string> lsNumericVals = new List<string>();
            List<string> lsDateTimes = new List<string>();
            List<string> lsDates = new List<string>();
            List<string> lsTimes = new List<string>();
            lsValues.ForEach((value) =>
            {
                string keyword;
                DateTime dateTime;
                if (value is string)
                {
                    keyword = value as string;
                    if (keyword.Trim() != string.Empty)
                        lsKeywords.Add("'''" + (isExact ? string.Empty : "%") + keyword.Trim().Replace("'", "''") + (isExact ? string.Empty : "%") + "'''");
                }
                else if (value is DateTime)
                {
                    dateTime = (DateTime)value;
                    lsDateTimes.Add("'''" + dateTime.ToString() + "'''");
                    lsDates.Add("'''" + dateTime.ToShortDateString() + "'''");
                    lsTimes.Add("'''" + dateTime.ToShortTimeString() + "'''");
                }
                else
                    lsNumericVals.Add("'" + value.ToString() + "'");
            }
            );
            if (lsKeywords.Count + lsNumericVals.Count + lsDateTimes.Count == 0)
                return null;
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                string sql =
                    string.Format(
                    @"  DECLARE @Results AS TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	                    SET NOCOUNT ON

	                    DECLARE @TableName nvarchar(256), @TableName2 nvarchar(258), " + (lsKeywords.Count == 0 ? string.Empty : "@StringColumnName nvarchar(128), @StringColumnName2 nvarchar(130), ") + (lsNumericVals.Count == 0 ? string.Empty : "@NumericColumnName nvarchar(128), @NumericColumnName2 nvarchar(130), ") + (lsDateTimes.Count == 0 ? string.Empty : "@DateTimeColumnName nvarchar(128), @DateTimeColumnName2 nvarchar(130), ") + (lsDates.Count == 0 ? string.Empty : "@DateColumnName nvarchar(128), @DateColumnName2 nvarchar(130), ") + (lsTimes.Count == 0 ? string.Empty : "@TimeColumnName nvarchar(128), @TimeColumnName2 nvarchar(130), ") + @"@SearchStr2 nvarchar(110)
	                    SET  @TableName = ''

	                    WHILE @TableName IS NOT NULL
	                    BEGIN
		                    " + (lsKeywords.Count == 0 ? string.Empty : "SET @StringColumnName = ''") + @"
		                    " + (lsNumericVals.Count == 0 ? string.Empty : "SET @NumericColumnName = ''") + @"
		                    " + (lsDateTimes.Count == 0 ? string.Empty : "SET @DateTimeColumnName = ''") + @"
		                    " + (lsDates.Count == 0 ? string.Empty : "SET @DateColumnName = ''") + @"
		                    " + (lsTimes.Count == 0 ? string.Empty : "SET @TimeColumnName = ''") + @"
		                    SET @TableName = 
		                    (
			                    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			                    FROM 	INFORMATION_SCHEMA.TABLES
			                    WHERE 		TABLE_TYPE = 'BASE TABLE'
				                    AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				                    AND	OBJECTPROPERTY(
						                    OBJECT_ID(
							                    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							                        ), 'IsMSShipped'
						                            ) = 0
		                    )

		                    WHILE (@TableName IS NOT NULL) AND (" + (lsKeywords.Count == 0 ? string.Empty : "@StringColumnName IS NOT NULL")
                                                                  + (lsKeywords.Count == 0 || (lsNumericVals.Count == 0 && lsDateTimes.Count == 0) ? string.Empty : " OR ")
                                                                  + (lsNumericVals.Count == 0 ? string.Empty : "@NumericColumnName IS NOT NULL ")
                                                                  + (lsNumericVals.Count == 0 || lsDateTimes.Count == 0 ? string.Empty : " OR ")
                                                                  + (lsDateTimes.Count == 0 ? string.Empty : "@DateTimeColumnName IS NOT NULL OR @DateColumnName IS NOT NULL OR @TimeColumnName IS NOT NULL")
                            + @") 
                                BEGIN" +
                                (lsKeywords.Count == 0 ? string.Empty : @"
			                    SET @StringColumnName =
			                    (
				                    SELECT MIN(QUOTENAME(COLUMN_NAME))
				                    FROM 	INFORMATION_SCHEMA.COLUMNS
				                    WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					                    AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					                    AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					                    AND	QUOTENAME(COLUMN_NAME) > @StringColumnName
			                    )
                                ") +
                                 (lsNumericVals.Count == 0 ? string.Empty : @"
			                    SET @NumericColumnName =
			                    (
				                    SELECT MIN(QUOTENAME(COLUMN_NAME))
				                    FROM 	INFORMATION_SCHEMA.COLUMNS
				                    WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					                    AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					                    AND	DATA_TYPE IN ('tinyint', 'smallint', 'int', 'bigint', 'real', 'float', 'decimal', 'numeric', 'money', 'smallmoney')
					                    AND	QUOTENAME(COLUMN_NAME) > @NumericColumnName
			                    )
                                ") +
                                 (lsDateTimes.Count == 0 ? string.Empty : @"
			                    SET @DateTimeColumnName =
			                    (
				                    SELECT MIN(QUOTENAME(COLUMN_NAME))
				                    FROM 	INFORMATION_SCHEMA.COLUMNS
				                    WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					                    AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					                    AND	DATA_TYPE IN ('datetime', 'datetime2', 'smalldatetime', 'datetimeoffset', 'timestamp')
					                    AND	QUOTENAME(COLUMN_NAME) > @DateTimeColumnName
			                    )
                                ") +
                                 (lsDates.Count == 0 ? string.Empty : @"
			                    SET @DateColumnName =
			                    (
				                    SELECT MIN(QUOTENAME(COLUMN_NAME))
				                    FROM 	INFORMATION_SCHEMA.COLUMNS
				                    WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					                    AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					                    AND	DATA_TYPE = 'date'
					                    AND	QUOTENAME(COLUMN_NAME) > @DateColumnName
			                    )
                                ") +
                                 (lsTimes.Count == 0 ? string.Empty : @"
			                    SET @TimeColumnName =
			                    (
				                    SELECT MIN(QUOTENAME(COLUMN_NAME))
				                    FROM 	INFORMATION_SCHEMA.COLUMNS
				                    WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					                    AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					                    AND	DATA_TYPE = 'time'
					                    AND	QUOTENAME(COLUMN_NAME) > @TimeColumnName
			                    )

                                SET @TableName2 =  REPLACE(@TableName,'''','''''')

                                ") +
                                (lsKeywords.Count == 0 ? string.Empty : @"
			                    IF @StringColumnName IS NOT NULL
			                    BEGIN
                                    SET @StringColumnName2 =  REPLACE(@StringColumnName,'''','''''')
				                    INSERT INTO @Results
				                    EXEC
				                    (
					                    'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @StringColumnName + ' LIKE ' + {0} + ') SELECT DISTINCT ''' + @TableName2 + '.' + @StringColumnName2 + ''', LEFT(' + @StringColumnName + ', 3630) 
					                    FROM ' + @TableName + ' (NOLOCK) WHERE ' + @StringColumnName + ' LIKE ' + {0}
				                    )
			                    END
                                ") +
                                 (lsNumericVals.Count == 0 ? string.Empty : @"
			                    IF @NumericColumnName IS NOT NULL
			                    BEGIN
                                    SET @NumericColumnName2 =  REPLACE(@NumericColumnName,'''','''''')
				                    INSERT INTO @Results
				                    EXEC
				                    (
					                    'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @NumericColumnName + ' = ' + {1} + ') SELECT DISTINCT ''' + @TableName2 + '.' + @NumericColumnName2 + ''', LEFT(' + @NumericColumnName + ', 3630) 
					                    FROM ' + @TableName + ' (NOLOCK) WHERE ' + @NumericColumnName + ' = ' + {1}
				                    )
			                    END
                                ") +
                                 (lsDateTimes.Count == 0 ? string.Empty : @"
			                    IF @DateTimeColumnName IS NOT NULL
			                    BEGIN
                                    SET @DateTimeColumnName2 =  REPLACE(@DateTimeColumnName,'''','''''')
				                    INSERT INTO @Results
				                    EXEC
				                    (
					                    'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @DateTimeColumnName + ' = ' + {2} + ') SELECT DISTINCT ''' + @TableName2 + '.' + @DateTimeColumnName2 + ''', LEFT(' + @DateTimeColumnName + ', 3630) 
					                    FROM ' + @TableName + ' (NOLOCK) WHERE ' + @DateTimeColumnName + ' = ' + {2}
				                    )
			                    END
                                ") +
                                 (lsDates.Count == 0 ? string.Empty : @"
			                    IF @DateColumnName IS NOT NULL
			                    BEGIN
                                    SET @DateColumnName2 =  REPLACE(@DateColumnName,'''','''''')
				                    INSERT INTO @Results
				                    EXEC
				                    (
					                    'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @DateColumnName + ' = ' + {3} + ') SELECT DISTINCT ''' + @TableName2 + '.' + @DateColumnName2 + ''', LEFT(' + @DateColumnName + ', 3630) 
					                    FROM ' + @TableName + ' (NOLOCK) WHERE ' + @DateColumnName + ' = ' + {3}
				                    )
			                    END
                                ") +
                                 (lsTimes.Count == 0 ? string.Empty : @"
			                    IF @TimeColumnName IS NOT NULL
			                    BEGIN
                                    SET @TimeColumnName2 =  REPLACE(@TimeColumnName,'''','''''')
				                    INSERT INTO @Results
				                    EXEC
				                    (
					                    'IF EXISTS(SELECT TOP 1 1 FROM ' + @TableName + ' (NOLOCK) WHERE ' + @TimeColumnName + ' = ' + {4} + ') SELECT DISTINCT ''' + @TableName2 + '.' + @TimeColumnName2 + ''', LEFT(' + @TimeColumnName + ', 3630) 
					                    FROM ' + @TableName + ' (NOLOCK) WHERE ' + @TimeColumnName + ' = ' + {4}
				                    )
			                    END
                                ") + @"
		                    END
	                    END

                        " + (withDataType ?
                          @"SELECT ColumnName, DATA_TYPE + 
                            CASE WHEN [CHARACTER_MAXIMUM_LENGTH] IS NULL THEN
                              CASE WHEN [DATA_TYPE] = 'decimal' THEN
                                '(' + CAST([NUMERIC_PRECISION] AS VARCHAR) + ',' + CAST([NUMERIC_SCALE] AS VARCHAR) + ')'
                              ELSE
                                ''
                              END
                            ELSE
                              '(' +
                              CASE WHEN [CHARACTER_MAXIMUM_LENGTH] = -1 THEN
                                'MAX'
                              ELSE
                                CAST([CHARACTER_MAXIMUM_LENGTH] AS VARCHAR)
                              END +
                              ')'
                            END [ColumnType], ColumnValue
                            FROM @Results R
                            INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON QUOTENAME(C.[TABLE_SCHEMA]) + '.' + QUOTENAME(C.[TABLE_NAME]) + '.' + QUOTENAME(C.[COLUMN_NAME]) = ColumnName
                            ORDER BY ColumnName"
                          :
                          "SELECT ColumnName, ColumnValue FROM @Results ORDER BY ColumnName")
                          , lsKeywords.Count == 0 ? string.Empty : lsKeywords.Aggregate((current, next) => current + @" + ' OR ' + @StringColumnName + ' LIKE ' + " + next)
                    , lsNumericVals.Count == 0 ? string.Empty : lsNumericVals.Aggregate((current, next) => current + @" + ' OR ' + @NumericColumnName + ' = ' + " + next)
                    , lsDateTimes.Count == 0 ? string.Empty : lsDateTimes.Aggregate((current, next) => current + @" + ' OR ' + @DateTimeColumnName + ' = ' + " + next)
                    , lsDates.Count == 0 ? string.Empty : lsDates.Aggregate((current, next) => current + @" + ' OR ' + @DateColumnName + ' = ' + " + next)
                    , lsTimes.Count == 0 ? string.Empty : lsTimes.Aggregate((current, next) => current + @" + ' OR ' + @TimeColumnName + ' = ' + " + next));
                return db.ExecuteWithResults(sql).Tables[0];
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            if (ex.HResult == -2146233088)
                return null;
            else
            {
                Disconnect();
                throw ex;
            }
        }
    }

    /// <summary>
    /// 在数据库的所有表的所有字段名称中过滤一个关键字，并返回符合条件的数据库字段名称
    /// </summary>
    /// <param name="keyword">关键字</param>
    /// <param name="isExact">是否精确搜索</param>
    /// <returns></returns>
    public DataTable DatabaseSearchColumnName(string name, bool isExact = false)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (name.Trim() == string.Empty)
                return null;
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                return db.ExecuteWithResults(
                    string.Format(
                    @"  SELECT [TABLE_SCHEMA],[TABLE_NAME],[COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE [TABLE_CATALOG] = '{0}' AND [COLUMN_NAME] LIKE '{1}'
                        ORDER BY [COLUMN_NAME]"
                    , DatabaseName
                    , (isExact ? string.Empty : "%") + name.Trim().Replace("'", "''") + (isExact ? string.Empty : "%"))
                    ).Tables[0];
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 在数据库的所有表的所有字段名称中过滤一个关键字，并返回符合条件的数据库字段名称
    /// </summary>
    /// <param name="keyword">关键字</param>
    /// <param name="isExact">是否精确搜索</param>
    /// <returns></returns>
    public DataTable DatabaseSearchColumnName(string[] names, bool isExact = false)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (names == null)
                return null;
            List<string> lsNames = new List<string>(names);
            List<string> lsNames2 = new List<string>();
            lsNames.ForEach((name) =>
            {
                if (name.Trim() != string.Empty)
                    lsNames2.Add("'" + (isExact ? string.Empty : "%") + name.Trim().Replace("'", "''") + (isExact ? string.Empty : "%") + "'");
            }
            );
            if (lsNames2.Count == 0)
                return null;
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                string sql = string.Format(
                    @"  SELECT [TABLE_SCHEMA],[TABLE_NAME],[COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE [TABLE_CATALOG] = '{0}' AND ([COLUMN_NAME] LIKE {1})
                        ORDER BY [COLUMN_NAME]"
                    , DatabaseName
                    , lsNames2.Aggregate((current, next) => current + @" OR [COLUMN_NAME] LIKE " + next));
                return db.ExecuteWithResults(sql).Tables[0];
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 按表查看数据库磁盘使用情况
    /// </summary>
    /// <returns></returns>
    public DataTable DatabaseDiskUsageByTable()
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                return db.ExecuteWithResults(
                    @"  BEGIN TRY 
                            SELECT
	                            a3.name AS [schemaname],
	                            a2.name AS [tablename],
	                            a1.rows as row_count,
	                            (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
	                            a1.data * 8 AS data,
	                            (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
	                            (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused,
	                            CASE WHEN a1.data = 0 THEN
	                            0
	                            ELSE
	                            (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END)*10000/(a1.reserved + ISNULL(a4.reserved,0))
	                            END
	                            unusedRate
                            FROM
	                            (SELECT 
		                            ps.object_id,
		                            SUM (
			                            CASE
				                            WHEN (ps.index_id < 2) THEN row_count
				                            ELSE 0
			                            END
			                            ) AS [rows],
		                            SUM (ps.reserved_page_count) AS reserved,
		                            SUM (
			                            CASE
				                            WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
				                            ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
			                            END
			                            ) AS data,
		                            SUM (ps.used_page_count) AS used
	                            FROM sys.dm_db_partition_stats ps
	                            GROUP BY ps.object_id) AS a1
                            LEFT OUTER JOIN 
	                            (SELECT 
		                            it.parent_id,
		                            SUM(ps.reserved_page_count) AS reserved,
		                            SUM(ps.used_page_count) AS used
	                                FROM sys.dm_db_partition_stats ps
	                                INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
	                                WHERE it.internal_type IN (202,204)
	                                GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
                            INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
                            INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
                            WHERE a2.type <> N'S' and a2.type <> N'IT'
                            ORDER BY unusedRate DESC
                        END TRY 
                        BEGIN CATCH 
                            SELECT 
	                            -100 AS l1
                            ,    1 AS schemaname 
                            ,       ERROR_NUMBER() as tablename
                            ,       ERROR_SEVERITY() as row_count
                            ,       ERROR_STATE() as reserved
                            ,       ERROR_MESSAGE() as data
                            ,       1 AS index_size
                            ,         1 AS unused 
                        END CATCH"
                    ).Tables[0];
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public StringCollection DatabaseCheckTables(RepairType repairType)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
                return db.CheckTables(repairType);
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public StringCollection DatabaseCheckTables(RepairType repairType, RepairOptions repairOptions)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
                return db.CheckTables(repairType, repairOptions);
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public StringCollection DatabaseCheckTables(RepairType repairType, RepairStructure repairStructure)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
                return db.CheckTables(repairType, repairStructure);
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    public StringCollection DatabaseCheckTables(RepairType repairType, RepairOptions repairOptions, RepairStructure repairStructure)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
                return db.CheckTables(repairType, repairOptions, repairStructure);
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 获取SQL Server数据库中的聚簇索引信息
    /// </summary>
    /// <returns></returns>
    public DataTable DatabaseClusteredIndexesInformation()
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            Connect();
            Database db = server.Databases[DatabaseName];
            if (db != null)
            {
                return db.ExecuteWithResults(
                    @"  SELECT t.name [表名],ix.name [聚簇索引名称],c.name [聚簇索引包括字段] from sys.indexes ix
                        INNER JOIN sys.tables t ON ix.object_id = t.object_id AND ix.type_desc = 'CLUSTERED' AND t.type = 'U'
                        INNER JOIN sys.index_columns ic ON ic.index_id = ix.index_id and ix.object_id = ic.object_id
                        INNER JOIN sys.columns c ON ic.column_id = c.column_id and ic.object_id = c.object_id
                        ORDER BY [表名]"
                    ).Tables[0];
            }
            else
                throw new InvalidDataException(DatabaseName);
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }

    /// <summary>
    /// 仅通过MDF数据文件还原SQL Server数据库
    /// </summary>
    /// <param name="overwrite">是否覆盖数据库</param>
    public Database RestoreDatabaseByOnlyMDF(bool overwrite = false)
    {
        try
        {
            if (DatabaseName == null || DatabaseName.Trim() == string.Empty)
                throw new ArgumentNullException(DatabaseName);
            if (Path == null || Path.Trim() == string.Empty)
                throw new ArgumentNullException(Path);
            if (!Directory.Exists(Path))
                throw new InvalidArgumentException(Path);
            if (Path[Path.Length - 1] != '\\')
                Path += '\\';

            Connect();

            Database currentDb = server.Databases[DatabaseName];

            //看是否数据库已经存在
            if (currentDb != null)
            {
                if (overwrite)
                {
                    server.ConnectionContext.SqlConnectionObject.ChangeDatabase(DatabaseName);
                    //Stop all processes running on the DataBase database
                    server.KillAllProcesses(DatabaseName);
                    currentDb.Drop();
                }
                else
                    throw new InvalidDataException(DatabaseName);
            }

            ExecuteNonQuery(string.Format("EXEC sp_attach_single_file_db {0},'{1}'", DatabaseName, Path));
            return server.Databases[DatabaseName];
        }
        catch (Exception ex)
        {
            Disconnect();
            throw ex;
        }
    }
}

